﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;

using System.Text;
//using Microsoft.SqlServer.Management.Smo;
//using Microsoft.SqlServer.Management.Common;

namespace Tab2Sql
{
    /// <summary>
    /// UNIMPLEMENTED
    /// As of November 2009, this is still work in progress. It is intended that this class will be used where the user does not want to output sql statements into a new table but instead insert into an existing Sql object. 
    /// </summary>
    public class IntoExisting
    {

 
        /// <summary>
        /// Table Name. Read only.
        /// </summary>
        public string TName
        {
            get { return target.Name; }
       
        }
        /// <summary>
        /// Column count in target table. Read only.
        /// </summary>
        public int TColCount
        {
            get { return target.Columns.Count; }
            
        }

        private StringBuilder insertStatement;

        public String InsertStatement
        {
            get { return insertStatement.ToString(); }
       
        }

       // Microsoft.SqlServer.Management.Smo.Table target;
        object target;
		/// <summary>
        /// Single Constructor.
        /// </summary>
        /// <param name="tableName"></param>
        public IntoExisting (string tableName)
        {
        	insertStatement = new StringBuilder ();

            //Tab2Sql.Classes.db.SqlSmo theSmo = new Tab2Sql.Classes.db.SqlSmo(tableName);
            
			//theSmo.MainBit();
            //target = theSmo.TargetTable;
            //getColumns();
            
        }
        private ArrayList primaryKey;

        private void getColumns()
        {
            primaryKey = new ArrayList();
            string simpleColumnList="";
            string fqName =target.Parent+".["+target.Owner+"].["+TName+"]";
            string tmpS = "INSERT INTO "+fqName+" (";
            string docS = "--" + DGen.DText.Replicate(" ", tmpS.Length - 2);
            string SelS = "SELECT "+Environment.NewLine;
            foreach (Column col in target.Columns)
            {
                 tmpS+="["+col.Name+"]";
                 simpleColumnList += "[" + col.Name + "]";
                
                //----
                if (col.DataType.SqlDataType == SqlDataType.Char || col.DataType.SqlDataType == SqlDataType.VarChar || col.DataType.SqlDataType == SqlDataType.VarCharMax)
                {
                    SelS += "         LEFT([" + col.Name + "]," + col.DataType.MaximumLength.ToString() + ") AS [" + col.Name + "]";

                    docS += col.DataType.MaximumLength;
                }
                if (col.DataType.SqlDataType == SqlDataType.Int || col.DataType.SqlDataType == SqlDataType.Money || col.DataType.SqlDataType == SqlDataType.Numeric)
                {
                    SelS += "         CASE WHEN ISNUMERIC([" + col.Name + "])=1 THEN [" + col.Name + "] ELSE -1000 END AS ["+col.Name+"]";

                }
                if (col.DataType.SqlDataType==SqlDataType.DateTime)
                {
                    SelS += "         CASE WHEN ISDATE([" + col.Name + "])=1 THEN [" + col.Name + "] ELSE GETDATE()-100 END AS [" + col.Name + "]";
   
                }
                if (!col.Nullable)
                {
                    if (col.InPrimaryKey)
                    {
                        primaryKey.Add(col.Name);
                        docS += "PK ";
                    }
                    docS += "NOT NULL";
                }
                else
                {
                    docS += "     ";
                }
                docS += DGen.DText.Replicate(" ", Math.Max(tmpS.Length - docS.Length,1));

                if (col.ID != TColCount)
                {
                    simpleColumnList+=", ";
                    tmpS += ", ";
                    SelS += "," + Environment.NewLine;
                }
                else
                {
                    string pkey="";
                    for (int i = 0; i < primaryKey.Count; i++)
                    {
			                object obj = primaryKey[i];
                            string c = obj.ToString();
                            pkey += c;
                            if (i + 1 < primaryKey.Count)
                            {
                                pkey += ",";
                            }
                    }
                    tmpS += ")" + Environment.NewLine;
                    SelS += "," + Environment.NewLine;
                    SelS += "        ROW_NUMBER() OVER (PARTITION BY " + pkey + "  ORDER BY " + pkey + ") AS RN001";
                    SelS += Environment.NewLine + " FROM TableSource" ;
                    SelS = "SELECT "+simpleColumnList+Environment.NewLine+" FROM " + Environment.NewLine+"(" + SelS + ") T1"+Environment.NewLine+" WHERE T1.RN001=1";
                    if (primaryKey.Count == 1)
                    {
                        SelS+= " AND T1."+pkey+" NOT IN (SELECT "+pkey+" FROM "+fqName+");";
                    }
                }   


            }
            insertStatement.AppendLine(tmpS);
            insertStatement.AppendLine(SelS);
        }


    }
}
